﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using ExamSystem.Mdeol;
using System.Data;
namespace ExamSystem.DAL
{
    public class TquestioDALn
    {
        /// <summary>
        /// 查询试题信息
        /// </summary>
        public List<Tquestion> mohuquert(int id, string name)
        {
            List<Tquestion> list = new List<Tquestion>();
            string sql = "select a.*,b.tkcname from tquestion a,tkecheng b where a.tkcid=b.tkcid ";
            if(id!=0)
            {
                sql+=" and a.tkcid="+id+"";
            }
            if(!string.IsNullOrEmpty(name))
            {
                sql += " and a.tqname like '%" + name + "%'";
            }
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tqId= int.Parse(dr["tqId"].ToString());
                int tkcId= int.Parse(dr["tkcId"].ToString());
                string tqName=dr["tqName"].ToString();
                string tqtype = dr["tqtype"].ToString();
                float tqScore = float.Parse(dr["tqScore"].ToString());
                string tqA = dr["tqA"].ToString();
                string tqB = dr["tqB"].ToString();
                string tqC = dr["tqC"].ToString();
                string tqD = dr["tqD"].ToString();
                string tkcName = dr["tkcName"].ToString();
                string tqAnswer = dr["tqAnswer"].ToString();
                Tkecheng tk=new Tkecheng(tkcId,tkcName);
                Tquestion tq=new Tquestion(tqId,tk,tqName,tqtype,tqScore,tqA,tqB,tqC,tqD,tqAnswer);
                list.Add(tq);
            }
            return list;
        }



        /// <summary>
        /// 查询试题信息
        /// </summary>
        public List<Tquestion> selectkckecheng()
        {
            List<Tquestion> list = new List<Tquestion>();
            string sql = "select a.*,b.tkcname from tquestion a,tkecheng b where a.tkcid=b.tkcid ";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tqId= int.Parse(dr["tqId"].ToString());
                int tkcId= int.Parse(dr["tkcId"].ToString());
                string tqName=dr["tqName"].ToString();
                string tqtype = dr["tqtype"].ToString();
                float tqScore = float.Parse(dr["tqScore"].ToString());
                string tqA = dr["tqA"].ToString();
                string tqB = dr["tqB"].ToString();
                string tqC = dr["tqC"].ToString();
                string tqD = dr["tqD"].ToString();
                string tqAnswer = dr["tqAnswer"].ToString();
                string tkcName = dr["tkcName"].ToString();
                Tkecheng tk = new Tkecheng(tkcId, tkcName);
                Tquestion tq = new Tquestion(tqId, tk, tqName, tqtype, tqScore, tqA, tqB, tqC, tqD, tqAnswer);
                 list.Add(tq);
            }
            return list;
        }




        /// <summary>
        /// 查询试题信息根据id
        /// </summary>
        public List<Tquestion> selectquetionid(int id)
        {
            List<Tquestion> list = new List<Tquestion>();
            string sql = "select a.*,b.tkcname from tquestion a,tkecheng b where a.tkcid=b.tkcid  and tqid="+id;
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tqId = int.Parse(dr["tqId"].ToString());
                int tkcId = int.Parse(dr["tkcId"].ToString());
                string tqName = dr["tqName"].ToString();
                string tqtype = dr["tqtype"].ToString();
                float tqScore = float.Parse(dr["tqScore"].ToString());
                string tqA = dr["tqA"].ToString();
                string tqB = dr["tqB"].ToString();
                string tqC = dr["tqC"].ToString();
                string tqD = dr["tqD"].ToString();
                string tqAnswer = dr["tqAnswer"].ToString();
                string tkcName = dr["tkcName"].ToString();
                Tkecheng tk = new Tkecheng(tkcId, tkcName);
                Tquestion tq = new Tquestion(tqId, tk, tqName, tqtype, tqScore, tqA, tqB, tqC, tqD, tqAnswer);
                list.Add(tq);
            }
            return list;
        }




        /// <summary>
        /// 根据id删除试题信息
        /// </summary>
        public int delete(int id)
        {
            string sql = "delete from tquestion where tqId=" + id;
            return DBhepter.ExecuteNonQuery(sql);
        }




        /// <summary>
        /// 根据id修改试题信息
        /// </summary>
        public int update(Tquestion t)
        {
            string sql = "update tquestion set tkcId="+t.TkcId.TkcId+",tqName='"+t.TqName+"',tqType='"+t.Tqtype+"',tqScore="+t.TqScore+",tqA='"+t.TqA+"',tqB='"+t.TqB+"',tqC='"+t.TqC+"',tqD='"+t.TqD+"',tqAnswer='"+t.TqAnswer+"' where tqId="+t.TqId;
            return DBhepter.ExecuteNonQuery(sql);
        }



        /// <summary>
        /// 根据添加试题信息
        /// </summary>
        public int insert(Tquestion t)
        {
            string sql = "insert into tquestion values("+t.TkcId.TkcId+",'"+t.TqName+"','"+t.Tqtype+"',"+t.TqScore+",'"+t.TqA+"','"+t.TqB+"','"+t.TqC+"','"+t.TqD+"','"+t.TqAnswer+"')";
            return DBhepter.ExecuteNonQuery(sql);
        }
       



        /// <summary>
        /// 查询试卷信息查看
        /// </summary>
        public List<Tquestion> selecttpger(int id)
        {
            List<Tquestion> list = new List<Tquestion>();
            string sql = " select a.*,b.tkcname from tquestion a,tkecheng b where a.tkcId=b.tkcId and a.tqId in(select tqId from tpagerquestion where tpId=" + id + ")";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tqId = int.Parse(dr["tqId"].ToString());
                int tkcId = int.Parse(dr["tkcId"].ToString());
                string tqName = dr["tqName"].ToString();
                string tqtype = dr["tqtype"].ToString();
                float tqScore = float.Parse(dr["tqScore"].ToString());
                string tqA = dr["tqA"].ToString();
                string tqB = dr["tqB"].ToString();
                string tqC = dr["tqC"].ToString();
                string tqD = dr["tqD"].ToString();
                string tqAnswer = dr["tqAnswer"].ToString();
                string tkcName = dr["tkcName"].ToString();
                Tkecheng tk = new Tkecheng(tkcId, tkcName);
                Tquestion tq = new Tquestion(tqId, tk, tqName, tqtype, tqScore, tqA, tqB, tqC, tqD, tqAnswer);
                list.Add(tq);
            }
            return list;
        }








        /// <summary>
        /// 查询试卷信息查看答案
        /// </summary>
        public List<Tquestion> selectanswer(int tpid,int tsid)
        {
            List<Tquestion> list = new List<Tquestion>();
            string sql = "select t1.*,t2.taAnswer from (select a.* from tquestion a where a.tqId in (select tqId from tpagerquestion where tpId="+tpid+")) t1 left join (select tqid,taAnswer from tanswer where tpId="+tpid+" and tsId="+tsid+") t2 on t1.tqId=t2.tqId";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tqId = int.Parse(dr["tqId"].ToString());
                string tqName = dr["tqName"].ToString();
                string tqtype = dr["tqtype"].ToString();
                float tqScore = float.Parse(dr["tqScore"].ToString());
                string tqA = dr["tqA"].ToString();
                string tqB = dr["tqB"].ToString();
                string tqC = dr["tqC"].ToString();
                string tqD = dr["tqD"].ToString();
                string tqAnswer = dr["tqAnswer"].ToString();
                string taanswew = dr["taAnswer"].ToString();
                Tquestion tq = new Tquestion(tqId, null, tqName, tqtype, tqScore, tqA, tqB, tqC, tqD,tqAnswer,taanswew);
                list.Add(tq);
            }
            return list;
        }
    }
}